Imports System.Data
Imports System.Data.OleDb
Imports RIBS.Code.DBCode
Imports RIBS.Code

Public Class WorkLoad
    Function GetAvailableWork(ByVal ActionList As String, ByVal StatusList As String, ByVal UserList As String, ByVal Stats As Boolean, ByVal Find As String) As DataSet
        Dim ds As New DataSet
        Try
            'If ActionList.Length = 0 Or StatusList.Length = 0 Or UserList.Length = 0 Then
            ' Return Nothing
            ' Else
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            '" (SelecT top 1 ActionHistory.[Note] from  ActionHistory WHERE RibsID = Ribs.RibsID ORDER BY ActionHistory.ActionID DESC) as [Note], " & _
            Dim SQL As String = "SELECT DISTINCT 0 AS IsRowVisible, " & _
                                " (SelecT top 1 Ucase(RD.UserID) from  RIBS RD WHERE Ribs.POLICY_NUMBER = RD.POLICY_NUMBER AND Ribs.RibsID <> RD.RibsID ORDER BY RD.RIBSID DESC) as LastUser,   " & _
                                " (SelecT top 1 ActionHistory.EntryDate from  ActionHistory WHERE RibsID = Ribs.RibsID ORDER BY ActionHistory.ActionID DESC) as EntryDate, " & _
                                " (SelecT top 1 ActionHistory.ActionID from  ActionHistory WHERE RibsID = Ribs.RibsID ORDER BY ActionHistory.ActionID DESC) as ActionID, " & _
                                " (SelecT top 1 ActionHistory.ContactMethod from  ActionHistory WHERE RibsID = Ribs.RibsID ORDER BY ActionHistory.ActionID DESC) as ContactMethod,  " & _
                                " (SelecT top 1 ActionHistory.[Action]  from  ActionHistory WHERE RibsID = Ribs.RibsID ORDER BY ActionHistory.ActionID DESC) as [Action], " & _
                                " (SelecT top 1 ActionHistory.[User] from  ActionHistory WHERE RibsID = Ribs.RibsID ORDER BY ActionHistory.ActionID DESC) as [User], " & _
                                " (SelecT top 1 ActionHistory.RibsState from  ActionHistory WHERE RibsID = Ribs.RibsID ORDER BY ActionHistory.ActionID DESC) as RibsState, " & _
                                " Ribs.*, LETTER_CODE.Description " & _
                                " FROM (LETTER_CODE RIGHT JOIN Ribs ON LETTER_CODE.LETTER_CODE = Ribs.LETTER_CODE) LEFT JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID " & _
                                " WHERE(RIBS.AMOUNT_DUE >= [LETTER_CODE].[MinDisplay]) "
            If Trim(Find).Length <> 0 Then
                SQL += " AND (Ribs.POLICY_NUMBER LIKE '%" & Find & "%' "
                SQL += " OR Ribs.MAILING_NAME LIKE '%" & Find & "%' "
                SQL += " OR Ribs.ADVISER_SURNAME LIKE '%" & Find & "%' )"
            End If

            '"SELECT DISTINCT 0 as IsRowVisible, ActionHistory.*, Ribs.*, LETTER_CODE.Description " & _
            '                           " FROM (LETTER_CODE RIGHT JOIN Ribs ON LETTER_CODE.LETTER_CODE = Ribs.LETTER_CODE) LEFT JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID  " & _
            '                          " WHERE ((((Select Top 1 ActionID  from  ActionHistory Where RibsID = Ribs.RibsID order By ActionHistory.EntryDate Desc))=[ActionHistory].[actionid]  " & _
            '                         " AND ((Ribs.AMOUNT_DUE)>=[LETTER_CODE].[MinDisplay])) "
            '& GetSystemFilter(ActionList, StatusList, UserList)
            '" Or ((Select Top 1 ActionID  from  ActionHistory Where RibsID = Ribs.RibsID order By ActionHistory.EntryDate Desc)) Is Null) " & _

            Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            Adapter.Fill(ds, "Work")
            'Test 
            'If Stats Then
            '    SQL = "SELECT DISTINCT  Ribs.MAILING_STATE , Count(Ribs.MAILING_STATE) , '' AS X " & _
            '                                    " FROM (LETTER_CODE RIGHT JOIN Ribs ON LETTER_CODE.LETTER_CODE = Ribs.LETTER_CODE) " & _
            '                                    " LEFT JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID  " & _
            '                                    " WHERE " & GetSystemFilter(ActionList, StatusList, UserList, True) & _
            '                                    " Group By MAILING_STATE " & _
            '                                    " "
            '    Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            '    Adapter.Fill(ds, "State")
            '    SQL = "SELECT DISTINCT Top 20 Ribs.ADVISER_SURNAME , Count(Ribs.ADVISER_SURNAME), Sum(AMOUNT_DUE) As AMOUNT_DUE" & _
            '                                    " FROM (LETTER_CODE RIGHT JOIN Ribs ON LETTER_CODE.LETTER_CODE = Ribs.LETTER_CODE) " & _
            '                                    " LEFT JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID  " & _
            '                                    " WHERE " & GetSystemFilter(ActionList, StatusList, UserList, True) & _
            '                                    " Group By ADVISER_SURNAME " & _
            '                                    " ORDER BY Count(Ribs.ADVISER_SURNAME) DESC"
            '    Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            '    Adapter.Fill(ds, "Adviser")
            'End If
            Return ds
            'End If
        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Function GetAvailableStats(ByVal ActionList As String, ByVal StatusList As String, ByVal UserList As String) As DataSet
        Dim ds As New DataSet
        Try
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Dim SQL As String = "SELECT DISTINCT ActionHistory.*, Ribs.*, LETTER_CODE.Description " & _
                                " FROM (LETTER_CODE RIGHT JOIN Ribs ON LETTER_CODE.LETTER_CODE = Ribs.LETTER_CODE) " & _
                                " LEFT JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID  " & _
                                " WHERE " & GetSystemFilter(ActionList, StatusList, UserList) & _
                                " ORDER BY AMOUNT_DUE DESC, ADVISER_AGENT_NUMBER, ADVISER_CLIENT_NUMBER, MAILING_NAME "
            Adapter.SelectCommand = New OleDbCommand(SQL, Conn)
            Adapter.Fill(ds, "Work")
            

        Catch ex As Exception
            Throw ex
        End Try
        Return ds
    End Function


End Class
